/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MBSCAction;
import org.compiere.model.MBSCActionAdditional;
import org.compiere.model.MBSCNetWorkDiag;
import org.compiere.model.MBSCNetWorkDiagLine;
import org.compiere.model.MBSCNetWorkDiagSubLine;
import org.compiere.model.MBSCResponsibleExecutor;
import org.compiere.model.MChangeNotice;
import org.compiere.model.MChat;
import org.compiere.model.MChatEntry;
import org.compiere.model.MOrg;
import org.compiere.model.MRefList;
import org.compiere.model.MUser;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 *
 */
public class BSC_NetWorkDiag_Report extends SvrProcess {
	//BSC_NetWorkDiag_Report
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	/**Current Record_ID */
	int p_BSC_NetWorkDiag_ID = 0;
	private Timestamp p_Period;
	private boolean shortVersion = true;
	/**Out file */
	String ResultPath;
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("BSC_NetWorkDiag_ID")) 
				p_BSC_NetWorkDiag_ID  = para[i].getParameterAsInt();
			else if (name.equals("DateAcct"))
				p_Period = (Timestamp) para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = Util.getResultPath("BSC_NetWorkDiag_Report", (new String("BSC_NetWorkDiag_Report").length())+1);
		// 
		  MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));
		
		XSSFWorkbook wb = new XSSFWorkbook(entry.getInputStream());
		XSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		
		if(p_BSC_NetWorkDiag_ID==0){
			sheet.createRow(14).createCell(0).setCellValue("Данные отсутвуют");
			sheet.getRow(14).getCell(0).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
		}
		else
		{
			MBSCNetWorkDiag netWorkDiag = new MBSCNetWorkDiag(m_ctx, p_BSC_NetWorkDiag_ID, get_TrxName());
			//Report Header
			sheet.getRow(1).getCell(0).setCellValue(netWorkDiag.getName());
			sheet.getRow(3).getCell(4).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(p_Period));
			sheet.getRow(5).getCell(4).setCellValue(new MOrg(m_ctx, netWorkDiag.getAD_Org_ID(), get_TrxName()).getName());
			sheet.getRow(6).getCell(4).setCellValue(netWorkDiag.getDescription());
			
			//
			int rowNo = 14,	nn = 1;;
			for(MBSCNetWorkDiagLine netWorkDiagLine : netWorkDiag.getLines(false))
			{
				for(MBSCNetWorkDiagSubLine netWorkDiagSubLine : netWorkDiagLine.getSubLines(false))
				{
				
					for(MBSCAction mbscAction : netWorkDiagSubLine.getActions(false))
					{
						sheet.shiftRows(rowNo, rowNo+10, 1);
						R = sheet.createRow(rowNo);
						for(int i=0; i<15; i++)
						{
							R.createCell(i);
							R.getCell(i).setCellStyle(sheet.getRow(0).getCell(0).getCellStyle());
						}
						//col1
						R.getCell(0).setCellValue(nn);
						//col2
						R.getCell(1).setCellValue(mbscAction.getSequence().floatValue());
						//R.getCell(0).setCellStyle(sheet.getRow(0).getCell(1).getCellStyle());
						//col3
						sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 2, 5));
						R.getCell(2).setCellStyle(sheet.getRow(0).getCell(2).getCellStyle());
						R.getCell(2).setCellValue(mbscAction.getName());
						//Row height set
//						int actionNameLenght = mbscAction.getName().length();
//						int hCount = 0;
//						int diff = actionNameLenght/30;
//						hCount = diff;
//						if(actionNameLenght - 30*diff > 0)
//							hCount = hCount+2;
//						R.setHeight((short) (sheet.getRow(0).getHeight()*hCount));
						//
						//col4
						R.getCell(6).setCellValue(mbscAction.getCompletionForm());
						//col5
						String executorsList="";
						int e=0;
						for(MBSCResponsibleExecutor executor : mbscAction.getResponsible_Executors(false))
						{
							if(e!=0)
								executorsList = executorsList+", ";
							//если ДЗО
							if(executor.getName()!=null)
							{
								executorsList = executorsList + executor.getName();
							}
							else{
								executorsList = executorsList+(executor.getAD_User().getC_BPartner().getName()==null?executor.getAD_User().getDescription():executor.getAD_User().getC_BPartner().getName());
							}
							//
							e++;
						}
						R.getCell(7).setCellValue(executorsList);
						//col6
						String performanceDate="date";
						String performanceDateList = "";
						
						if(mbscAction.isDistributed()){
							int d = 0;
							for(MBSCActionAdditional mbscActionAdditional:mbscAction.getActionAdditionals(false)){
								if(d !=0)
									performanceDateList = performanceDateList + ", ";
								//
								String tempPerformanceDate = DB.getSQLValueString(get_TrxName()
											, "SELECT erps_periodrange(datefrom,dateto) " +
													"FROM BSC_ActionAdditional WHERE BSC_ActionAdditional_ID = ?"
													, mbscActionAdditional.getBSC_ActionAdditional_ID());
								performanceDateList = performanceDateList + tempPerformanceDate;
								//
								d++;
							}
							R.getCell(8).setCellValue(performanceDateList);
						}
						else{
							performanceDate = DB.getSQLValueString(get_TrxName()
									, "SELECT erps_periodrange(datefrom,dateto) FROM BSC_Action WHERE BSC_Action_ID = ?"
									, mbscAction.getBSC_Action_ID());
							R.getCell(8).setCellValue(performanceDate);
						}
						//col7
						//info from Chat
						StringBuffer executeInfo = new StringBuffer();
						executeInfo.append(MRefList.getListName(m_ctx, 1000152, mbscAction.getDocStatus()));
						executeInfo.append("\n");
						String sql = "SELECT CM_Chat_ID FROM CM_Chat WHERE AD_Table_ID="+MBSCAction.Table_ID+" AND Record_ID="+mbscAction.getBSC_Action_ID();
						int p_CM_Chat_ID = DB.getSQLValue(get_TrxName(), sql);
						if(p_CM_Chat_ID>0){
							MChatEntry chatEntry = null;
							String entriesSql = "SELECT CM_Chatentry_ID FROM CM_Chatentry WHERE CM_Chat_ID="+p_CM_Chat_ID;
							PreparedStatement pstmt = null;
							ResultSet rs = null;
							try{
								pstmt = DB.prepareStatement(entriesSql, get_TrxName());
								rs = pstmt.executeQuery();
								while(rs.next()){
									chatEntry = new MChatEntry(m_ctx, rs.getInt(1), get_TrxName());
									MBPartner mbPartner = new MBPartner(m_ctx, new MUser(m_ctx, chatEntry.getUpdatedBy(), get_TrxName()).getC_BPartner_ID(), get_TrxName());
									executeInfo.append(mbPartner.getName());
									executeInfo.append("\n");
									executeInfo.append(chatEntry.getCharacterData());
									executeInfo.append("\n");
								}
								
							}catch(SQLException ex){
								log.log(Level.SEVERE, ex.toString(), entriesSql.toString());
							}finally{
								DB.close(rs, pstmt);
								rs = null; pstmt = null;
							}
							
						}
						//
						sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 9, 14));
						R.getCell(9).setCellStyle(sheet.getRow(0).getCell(2).getCellStyle());
						R.getCell(9).setCellValue(new String(executeInfo));
						
						rowNo++;nn++;
						//AD_Reference_ID = 1000152
					}
				}
			}
			
			if(shortVersion){
				sheet.groupRow(8, 12);
				sheet.setRowGroupCollapsed(8, true);
				sheet.groupRow(rowNo, rowNo+10);
				sheet.setRowGroupCollapsed(rowNo, true);
			}	
			
		}
		//delete temp styles
		sheet.getRow(0).getCell(0).setCellStyle(sheet.getRow(0).getCell(6).getCellStyle());
		sheet.getRow(0).getCell(1).setCellStyle(sheet.getRow(0).getCell(6).getCellStyle());
		sheet.getRow(0).getCell(2).setCellStyle(sheet.getRow(0).getCell(6).getCellStyle());
		//
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));	
		return Msg.translate(m_ctx, "Success");
	}
	
}
